<?php

class Threads extends \Phalcon\Mvc\Model
{

    /**
     *
     * @var integer
     */
    public $id;

    /**
     *
     * @var integer
     */
    public $title;

    /**
     *
     * @var string
     */
    public $tags;

    /**
     *
     * @var string
     */
    public $content;

    /**
     *
     * @var integer
     */
    public $user_id;

    /**
     *
     * @var double
     */
    public $views;

    /**
     *
     * @var string
     */
    public $last_reply;

    /**
     *
     * @var string
     */
    public $date_created;

    /**
     *
     * @var string
     */
    public $category;

    /**
     *
     * @var integer
     */
    public $status;

    /**
     *
     * @var string
     */
    public $pictures;


    /**
     * Independent Column Mapping.
     */
    public function columnMap()
    {
        return array(
            'id' => 'id', 
            'title' => 'title', 
            'tags' => 'tags', 
            'content' => 'content', 
            'user_id' => 'user_id', 
            'views' => 'views', 
            'last_reply' => 'last_reply', 
            'date_created' => 'date_created', 
            'category' => 'category', 
            'status' => 'status', 
            'pictures' => 'pictures'
        );
    }

    public function beforeValidationOnCreate()
    {
        $this->date_created = CURR_DATE;
        $this->status = 1;
        $this->views = 0;
    }

    public function listThreadsByUserId($user_id)
    {
        $phql = "SELECT t.id,t.title,t.tags,t.content,t.user_id,
                t.views,t.last_reply,t.date_created,t.category,
                t.status,t.pictures,uat.username
                FROM Threads t
                LEFT JOIN UserAccountTb uat ON uat.id = t.user_id
                WHERE t.user_id = ?0
                ORDER BY t.date_created DESC";
        $data = $this->modelsManager->executeQuery($phql,array(0=>$user_id));
        return $data;
    }

    public function listThreads()
    {
        $phql = "SELECT t.id,t.title,t.tags,t.content,t.user_id,
                t.views,t.last_reply,t.date_created,t.category,
                t.status,t.pictures,uat.username,COUNT(tr.forum_id) as replies
                FROM Threads t
                LEFT JOIN UserAccountTb uat ON uat.id = t.user_id
                LEFT JOIN ThreadsReply tr on tr.forum_id = t.id
                GROUP BY t.id
                ORDER BY t.date_created DESC";
        $data = $this->modelsManager->executeQuery($phql,array(0=>$user_id));
        return $data;
    }

    public function listThreadsInfo($thread_id)
    {
        $phql = "SELECT t.id,t.title,t.tags,t.content,t.user_id,
                t.views,t.last_reply,t.date_created,t.category,
                t.status,t.pictures,uat.username,uat.profile_pic
                FROM Threads t
                LEFT JOIN UserAccountTb uat ON uat.id = t.user_id
                WHERE t.id = ?0
                ORDER BY t.date_created DESC";
        $data = $this->modelsManager->executeQuery($phql,array(0=>$thread_id));
        return $data;
    }

    public function countMyThreads($user_id)
    {
        $phql = "SELECT COUNT(t.id) as total FROM Threads t WHERE t.user_id = ?0";
        $data = $this->modelsManager->executeQuery($phql,array(0=>$user_id));
        return $data[0]->total;   
    }
}

